package com.boc.ewr.business.mapper;

import java.util.List;

import com.boc.ewr.business.entity.LineAccessList;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Select;

/**
 * @Description: 《条线考核数据汇总》
 * @Author: jeecg-boot
 * @Date:   2023-04-20
 * @Version: V1.0
 */
public interface LineAccessListMapper extends BaseMapper<LineAccessList> {

    //签约数
    @Select("select dp.PARENT_ID as PARENT_BRANCH_NO, p.SIGNED_ORG as BRANCH_NO, '对账指标-签约及时率考核' as CHECK_CONTENT, COUNT(p.ID) from PRO p " +
            "LEFT JOIN SYS_DEPART dp ON p.SIGNED_ORG = dp.ID " +
            "where LINKER_NAME is NULL GROUP BY dp.PARENT_ID, p.SIGNED_ORG")
    List<LineAccessList> creatSigningInfo();

    //上门对账完成率考核
    //A1
    @Select("select PARENT_BRANCH_NO, BRANCH_NO, '对账指标-上门对账完成数' as CHECK_CONTENT, COUNT(ID) FROM ACCT_DTL_CLASS_A1 where CHECK_PSN != NULL GROUP BY PARENT_BRANCH_NO, BRANCH_NO")
    List<LineAccessList> creatA1BillInfo();

    //A2
    @Select("select PARENT_BRANCH_NO, BRANCH_NO, '对账指标-上门对账完成数' as CHECK_CONTENT, COUNT(ID) FROM ACCT_DTL_CLASS_A2 where CHECK_PSN != NULL GROUP BY PARENT_BRANCH_NO, BRANCH_NO")
    List<LineAccessList> creatA2BillInfo();

    //A3
    @Select("select PARENT_BRANCH_NO, BRANCH_NO, '对账指标-上门对账完成数' as CHECK_CONTENT, COUNT(ID) FROM ACCT_DTL_CLASS_A3 where CHECK_PSN != NULL GROUP BY PARENT_BRANCH_NO, BRANCH_NO")
    List<LineAccessList> creatA3BillInfo();

    //季度报告
    @Select("select QR_PARENT_BRANCH_NO as PARENT_BRANCH_NO, QR_BRANCH_NO as BRANCH_NO, '上传日期超过设定逾期时间次数' as CHECK_CONTENT, COUNT(ID) FROM EWR_QUARTERLY_REPORT where CREATE_TIME > #{deadline} GROUP BY QR_PARENT_BRANCH_NO, QR_BRANCH_NO")
    List<LineAccessList> creatReportInfo(String deadline);

    //条线履职-风险核查反馈
    @Select("select PARENT_BRANCH_NO, BRANCH_NO, '条线履职-风险核查反馈' as CHECK_CONTENT, COUNT(ID) FROM ACCT_DTL_CLASS_C where CHECK_PSN != NULL GROUP BY PARENT_BRANCH_NO, BRANCH_NO")
    List<LineAccessList> creatRiskInfo();

}
